<?php
declare (strict_types = 1);

namespace app\home\controller;

use app\base\BaseController;
use app\home\validate\EnterpriseCheck;
use app\home\validate\GoodsCheck;
use app\home\validate\ItemCheck;
use app\home\validate\UserWorkHoursCheck;
use app\user\model\Admin;
use Overtrue\Pinyin\Pinyin;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Shared\Date as Shared;
use think\exception\ValidateException;
use think\facade\Db;
use think\facade\View;

class Wages extends BaseController
{
    /**
     * 工资管理
     */
    public function index()
    {
        $param = get_params();

        View::assign('date', date('Y'));

        if (request()->isAjax()) {

            $where = [];

            $where['eid']    = $this->eid;
            $where['status']    = 0;
            $where['year']      = !empty($param['year']) ? $param['year'] : date('Y');

            //合计工时
            $work_user = Db::name('UserWorkHours')->where($where)->group('month')->field('year,month,sum(work_hours) as all_time,eid')->select()->toArray();
            if(!empty($work_user)){
                //查询工资，公积金，
                foreach($work_user as $k => &$v){
                    $sum = Db::name('Wages')
                        ->field('sum(wages) as wages , sum(accumulation_fund) as accumulation_fund , sum(social) as social ')
                        ->where(['eid'=>$v['eid'],'year'=>$v['year'] , 'month' => $v['month'] , 'status' => 0])
                        ->find();

                    $v['wages']                 = $sum['wages'] ?? 0;
                    $v['accumulation_fund']     = $sum['accumulation_fund'] ?? 0;
                    $v['social']                = $sum['social'] ?? 0;
                    $v['sum']                   = $v['wages'] + $v['accumulation_fund'] + $v['social'];
                }
            }

            $data['data'] = $work_user;

            return table_assign(0, '', $data);
        }else{
            return view();
        }
    }

    /**
     * 添加/编辑固定资产工时
     */
    public function operation()
    {
        $param = get_params();

        if (request()->isAjax()) {

            $year = $param['year'] ?? date('Y');
            $month = $param['month'] ?? date('m');

            $eid = $this->eid;

            $count = Db::name('Wages')->where(['eid' => $eid ,'year' => $year , 'month' => $month])->count();

            if($count > 0){
                Db::name('Wages')->where(['eid' => $eid ,'year' => $year , 'month' => $month])->delete();
            }

            $wages = json_decode($param['wages'] , true);

            foreach($wages as $key => &$value){

                $value['eid']                   = $eid;
                $value['time']                  = strtotime("{$year}-{$month}");
                $value['create_time']           = time();
                $value['year']                  = $year;
                $value['month']                  = $month;
                $value['create_time']           = time();

            }

            $insertId = Db::name('Wages')->strict(false)->field(true)->insertAll($wages);

            if ($insertId) {
                add_log('add', '', $param);
            }
            return to_assign();
        }else {

            $year       = $param['year'];
            $month      = $param['month'];

            //查询当前月份是否存在已添加的数据
            $where = [
                'uwh.eid' => $this->eid,
                'uwh.year' => $year,
                'uwh.month' => $month,
                'uwh.status' => 0,
            ];
            $user = Db::name('UserWorkHours')->alias('uwh')
                ->where($where)
                ->group('uwh.uid')
                ->field('uwh.eid,uwh.uid as id,u.name,sum(uwh.work_hours) as work_hours,uwh.year,uwh.month')
                ->join('User u' , 'u.id = uwh.uid')
                ->select()->toArray();

            //查询工资
            foreach($user as $k => &$v){
                $whe = ['year' => $v['year'] , 'month' => $v['month'] , 'status' => 0 , 'eid' => $v['eid'] , 'uid' => $v['id']];
                $wages = Db::name('Wages')->where($whe)->find();
                $v['wages'] = $wages['wages'] ?? 0;
                $v['accumulation_fund'] = $wages['accumulation_fund'] ?? 0;
                $v['social'] = $wages['social'] ?? 0;
            }
            View::assign('user', $user);

            return view();

        }
    }

    //导入工资
    public function fileWages(){

        $eid = $this->eid;

        // 获取表单上传文件
        $file[]= request()->file('file');

        try {
            // 验证文件大小，名称等是否正确
            validate(['file' => 'filesize:51200|fileExt:xls,xlsx'])->check($file);
            // 日期前綴
            $dataPath = date('Ym');
            $md5 = $file[0]->hash('md5');
            $savename = \think\facade\Filesystem::disk('public')->putFile($dataPath, $file[0], function () use ($md5) {
                return $md5;
            });
            $fileExtendName = substr(strrchr($savename, '.'), 1);
            // 有Xls和Xlsx格式两种
            if ($fileExtendName == 'xlsx') {
                $objReader = IOFactory::createReader('Xlsx');
            } else {
                $objReader = IOFactory::createReader('Xls');
            }
            $objReader->setReadDataOnly(TRUE);
            $path = get_config('filesystem.disks.public.url');
            // 读取文件，tp6默认上传的文件，在runtime的相应目录下，可根据实际情况自己更改
            $objPHPExcel = $objReader->load('.'.$path . '/' .$savename);
            $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
            $highestRow = $sheet->getHighestRow();       // 取得总行数
            $highestColumn = $sheet->getHighestColumn();   // 取得总列数
            Coordinate::columnIndexFromString($highestColumn);
            $lines = $highestRow - 1;

            if ($lines <= 0) {
                return to_assign(1, '数据不能为空');
                exit();
            }


            //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
            $pinyin = new Pinyin();
            for ($j = 4; $j <= $highestRow; $j++) {

                $uid    = $objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue();//员工id
                $name    = $objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue();//员工名称
                $year   = $objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue();//年
                $month  = $objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue();//月
                $wages  = $objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue();//工资
                $accumulation_fund  = $objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue();//社保
                $social  = $objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue();//公积金

                $info = [
                    'eid'               => $eid,
                    'uid'               => $uid,
                    'year'              => $year,
                    'month'             => $month,
                    'wages'             => $wages,
                    'accumulation_fund' => $accumulation_fund,
                    'social'            => $social,
                    'time'            => time(),
                    'create_time'            => time(),
                ];

                if(empty($uid)){
                    return to_assign(1, '第'.($j - 1).'行的员工id为必填项');
                }
                if(empty($year)){
                    return to_assign(1, '第'.($j - 1).'行的当前年为必填项');
                }
                if(empty($month)){
                    return to_assign(1, '第'.($j - 1).'行的当前月为必填项');
                }

                $where = [
                    'eid' => $eid,
                    'year' => $year,
                    'month' => $month,
                    'uid' => $uid
                ];

                $count = DB::name('Wages')->where($where)->count();

                if($count > 0){
                    $res = DB::name('Wages')->where($where)->update($info);
                }else{
                    $res = DB::name('Wages')->where($where)->insert($info);
                }

                if(!$res){
                    return to_assign(1, '第'.($j - 2).'行的数据添加出错，请检查excel文件再试');
                }
            }

            return to_assign(0, '导入成功');

        } catch (\think\exception\ValidateException $e) {
            return to_assign(1, $e->getMessage());
        }
    }


}
